/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cheapflix.dao;

import com.cheapflix.beans.DVD;
import com.cheapflix.beans.UserDVD;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * DAO class to manage user/dvd queue .
 * @author Group B
 */
public class UserDVDDao extends AbstractDAO {

    private static final Logger logger = Logger.getLogger(UserDVDDao.class);

    public UserDVDDao() {
    }

    @Autowired
    public UserDVDDao(DataSource ds) {
        super.init(ds);
    }

    /**
     * marshaller method
     */
    private static UserDVD getUserDVD(ResultSet rs) throws SQLException {
        if (rs == null) {
            return null;
        }
        UserDVD userDvd = new UserDVD();
        userDvd.setComment(rs.getString("comment"));
        userDvd.setDvdId(rs.getInt("dvd_id"));
        userDvd.setId(rs.getInt("id"));
        userDvd.setRating(rs.getInt("rating"));
        userDvd.setUserId(rs.getInt("user_id"));
        return userDvd;
    }

    /**
     * This method is used to both rate a DVD that's already been added to a user's
     * queue or simply adding a DVD in a user's queue (rating must be 0 and comment 
     * must be blank.
     * @param userId
     * @param dvdId
     * @param rating
     * @param comment
     * @return 
     */
    public boolean rateDVD(int userId, int dvdId, int rating, String comment) {
        String sql = "insert into user_dvd (user_id, dvd_id, rating, comment) values (%s,%s,%s,'%s') on duplicate key update rating=" + rating + ", comment='" + comment + "'";
        if (comment == null) {
            comment = "";
        }
        sql = String.format(sql, userId, dvdId, rating, comment);
        Connection conn = null;
        Statement stmt;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return false;
        }
        return true;
    }

    /**
     * get a user's flick queue.
     */
    public List<UserDVD> getUserDVDQueue(int userID) {
        logger.info("---------------- Inside getUserDVDQueue ----------------");
        List<UserDVD> ratedDvds = new ArrayList<UserDVD>();
        String sql = "select * from user_dvd where user_id = " + userID;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.debug(">sql: " + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                UserDVD udvd = getUserDVD(rs);
                logger.info("UDVD: " + udvd );
                //grab dvd information
                ResultSet rs2 = null;
                Statement stmt2 = null;
                try {

                    stmt2 = conn.createStatement();

                    sql = "select * from dvd where id=" + udvd.getDvdId();
                    logger.info(">sql: " + sql);
                    rs2 = stmt2.executeQuery(sql);
                    logger.info("Executed DVD sql!!!");
                    if( rs2.next() ){
                          DVD dvd = DVDDao.ResultSetToDVD(rs2);
                    logger.info("Fetched DVD: " + dvd);
                    udvd.setDvd(dvd);
                    }
                  
                } catch (SQLException ex) {
                    logger.error(ex.getMessage(), ex);
                    return null;
                } finally {
                    close(stmt2, rs2);
                }
                ratedDvds.add(udvd);
            }
            return ratedDvds;
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        } finally {
            close(conn, stmt, rs);
        }
    }

    /**
     * calculate avg rating for a movie from friends' ratings.
     * @param dvdId
     * @param meId
     * @return 
     */
    public double getFriendsAverageRatingForMovie(int dvdId, int meId) {
        String sql = "select avg(a.rating) from user_dvd a, flixfriends b where a.dvd_id=" + dvdId + " and a.user_id=b.friend_id and b.user_id=" + meId;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                double friendsRating = rs.getDouble(1);
                return friendsRating;
            } else {
                return -1.0;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return -1.0;
        } finally {
            close(conn, stmt, rs);
        }

    }

    /**
     * get a global avg rating.
     */
    public double getGlobalAverageRatingForMovie(int dvdId) {
        String sql = "select avg(rating) from user_dvd where dvd_id=" + dvdId;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                return rs.getDouble(1);
            } else {
                return -1.0;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return -1.0;
        } finally {
            close(conn, stmt, rs);
        }
    }

    public UserDVD getUsersDVDQueueEntryByDVDID(int userId, int dvdId) {
        String sql = "select * from user_dvd where user_id = " + userId + " and dvd_id = " + dvdId;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDataSource().getConnection();
            stmt = conn.createStatement();
            logger.info("> sql: " + sql);
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                UserDVD udvd = getUserDVD(rs);
                return udvd;
            } else {
                return null;
            }
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            return null;
        } finally {
            close(conn, stmt, rs);
        }
    }
}
